package util.util;

import util.model.common.DBConnModel;
import util.model.common.FieldInfo;
import util.model.common.TableInfo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

/**
 * @author liW
 * @version 1.0
 * @date 2019-7-23 10:33
 */
public class DevSqlUtil {



    public static String creatTableSql(List<List<FieldInfo>> list) {
        StringBuilder sb = new StringBuilder();
        for (List<FieldInfo> fieldInfoList : list) {
            String addSql = DevSqlUtil.createTabelSql(fieldInfoList);
            sb.append(addSql);
            sb.append("\n");
        }
        return sb.toString();
    }

    /**
     * 创建表
     *
     * @param fieldInfoList 表信息
     * @return sql
     */
    public static String createTabelSql(List<FieldInfo> fieldInfoList) {

        FieldInfo info = fieldInfoList.get(0);
        String tableName = info.getTableDBName();

        StringBuilder sb = new StringBuilder();

        // 创建表
        String tableSql = "CREATE TABLE %s(\n";
        tableSql = String.format(tableSql, tableName);
        sb.append(tableSql);

        // 创建字段
        for (FieldInfo fieldInfo : fieldInfoList) {
            sb.append(getFieldSql(fieldInfo));
        }

        // 添加结尾
        sb = new StringBuilder(sb.substring(0, sb.length() - 2));
        sb.append("\n);\n");

        // 添加注释
        sb.append(getTitleSql(fieldInfoList));

        return sb.toString();
    }

    /**
     * 创建字段
     *
     * @param fieldInfo 字段信息
     * @return sql
     */
    public static String getFieldSql(FieldInfo fieldInfo) {
        String name = fieldInfo.getFieldDBName();
        String type = fieldInfo.getFieldDBType();
        String must = fieldInfo.getFieldDBMust();
        String defaultValue = fieldInfo.getFieldDefaultValue();
        String title = fieldInfo.getFieldTitle();

        StringBuilder sb = new StringBuilder();
        sb.append(name);
        sb.append(" ");
        sb.append(type);
        if ("UUID".equals(title.toUpperCase())) {
            sb.append("  PRIMARY KEY ");
        } else {
            // 约束
            if (DevComUtil.notEmpty(defaultValue)) {
                if ("'".equals(defaultValue)) {
                    defaultValue = " ";
                }
                if (type.toUpperCase().contains("VARCHAR")) {
                    sb.append(" DEFAULT ");
                    sb.append(" '");
                    sb.append(defaultValue);
                    sb.append("'");
                } else {
                    sb.append(" DEFAULT ");
                    sb.append(defaultValue);
                }
            }
            if ("Y".equals(must)) {
                sb.append(" NOT NULL ");
            }
        }
        sb.append(",\n");
        return sb.toString();
    }


    /**
     * 字段注释---添加
     *
     * @param fieldInfoList 需要添加的字段集合
     * @return sql
     */
    public static String getTitleSql(List<FieldInfo> fieldInfoList) {
        StringBuilder sb = new StringBuilder();

        // 表明 注释
        sb.append(getTableTitleSql(fieldInfoList.get(0)));

        // 字段 注释
        for (FieldInfo fieldInfo : fieldInfoList) {
            sb.append(getFieldTitleSql(fieldInfo));
        }
        return sb.toString();
    }

    /**
     * 字段 注释
     *
     * @param fieldInfo 字段信息
     * @return sql
     */
    public static String getFieldTitleSql(FieldInfo fieldInfo) {
        String tableDBName = fieldInfo.getTableDBName();
        String name = fieldInfo.getFieldDBName();
        String title = fieldInfo.getFieldTitle();
        String comment = "COMMENT ON COLUMN %s.%s IS '%s'; \n";
        return String.format(comment, tableDBName, name, title);
    }

    /**
     * 表明注释
     *
     * @param fieldInfo 表信息
     * @return sql
     */
    public static String getTableTitleSql(FieldInfo fieldInfo) {
        String tableDBName = fieldInfo.getTableDBName();
        String name = fieldInfo.getTableTitle();
        // 表明 注释
        String table = "COMMENT ON TABLE %s  IS  '%s';\n";
        return String.format(table, tableDBName, name);
    }

    /**
     * 表明注释
     *
     * @param tableInfo 表信息
     * @return sql
     */
    public static String getTableTitleSql(TableInfo tableInfo) {
        String tableDBName = tableInfo.getTableDBName();
        String name = tableInfo.getTableTitle();
        // 表明 注释
        String table = "COMMENT ON TABLE %s  IS  '%s';\n";
        return String.format(table, tableDBName, name);
    }


    /**
     * 修改表---添加字段
     *
     * @param fieldInfoList 字段信息
     * @return sql
     */
    public static String alterAddFieldSql(List<FieldInfo> fieldInfoList) {
        StringBuilder sb = new StringBuilder();
        for (FieldInfo fieldInfo : fieldInfoList) {
            sb.append(alterAddFieldSql(fieldInfo));
        }
        return sb.toString();
    }

    public static String alterFieldBean(List<FieldInfo> fieldInfoList) {
        StringBuilder sb = new StringBuilder();
        for (FieldInfo fieldInfo : fieldInfoList) {
            sb.append(alterFieldBean(fieldInfo));
        }
        return sb.toString();
    }

    public static String alterFieldBean(FieldInfo info) {
        String fieldDBName = info.getFieldDBName();
        String fieldName = info.getFieldName();
        if (DevComUtil.isEmpty(fieldDBName)) {
            fieldDBName = DevComUtil.hump2line(fieldDBName).toUpperCase();
        }
        if (DevComUtil.isEmpty(fieldName)) {
            fieldName = DevComUtil.line2Hump(fieldDBName);
        }
        String fieldType = info.getFieldType();
        String fieldDBType = info.getFieldDBType().toUpperCase();
        if (DevComUtil.isEmpty(fieldType)) {
            if (fieldDBType.contains("VARCHAR")) {
                fieldType = "String";
            } else if (fieldDBType.contains("DATE") || fieldDBType.contains("TIMESTAMP")) {
                fieldType = "Date";
            } else if (fieldDBType.contains("NUMBER")) {
                fieldType = "BigDecimal";
            }
        }
        String xml = "    @Column(name = \"%s\")\n" +
                "    private %s %s;\n\n";
        return String.format(xml, fieldDBName.toUpperCase(), fieldType, fieldName);
    }

    public static String alterFieldXML(List<FieldInfo> fieldInfoList) {
        StringBuilder sb = new StringBuilder();
        for (FieldInfo fieldInfo : fieldInfoList) {
            sb.append(alterFieldXML(fieldInfo));
        }
        return sb.toString();
    }

    public static String alterFieldXML(FieldInfo info) {
        String fieldDBName = info.getFieldDBName();
        String fieldName = info.getFieldName();
        if (DevComUtil.isEmpty(fieldDBName)) {
            fieldDBName = DevComUtil.hump2line(fieldName);
        }

        if (DevComUtil.isEmpty(fieldName)) {
            fieldName = DevComUtil.line2Hump(fieldDBName);
        }
        String fieldType = info.getFieldType();
        String fieldDBType = info.getFieldDBType().toUpperCase();
        if (DevComUtil.isEmpty(fieldType)) {
            if (fieldDBType.contains("VARCHAR")) {
                fieldType = "VARCHAR";
            } else if (fieldDBType.contains("DATE") || fieldDBType.contains("TIMESTAMP")) {
                fieldType = "TIMESTAMP";
            } else if (fieldDBType.contains("NUMBER")) {
                fieldType = "DECIMAL";
            }
        }
        String xml = "<result column=\"%s\" jdbcType=\"%s\" property=\"%s\" />\n";
        return String.format(xml, fieldDBName.toUpperCase(), fieldType, fieldName);
    }

    /**
     * 修改表---添加字段
     *
     * @param info 字段信息
     * @return sql
     */
    public static String alterAddFieldSql(FieldInfo info) {
        StringBuilder sb = new StringBuilder();
        String tableDBName = info.getTableDBName();
        String name = info.getFieldDBName();
        String type = info.getFieldDBType();
        String must = info.getFieldDBMust();
        String defaultValue = info.getFieldDefaultValue();
        String title = info.getFieldTitle();

        // 添加一列
        String field = "ALTER TABLE %s ADD (%s %s);";
        field = String.format(field, tableDBName, name, type);
        sb.append(field);
        sb.append("\n");

        // 给列添加注释
        sb.append(getFieldTitleSql(info));

        // 约束
        if (DevComUtil.notEmpty(defaultValue)) {
            if ("'".equals(defaultValue)) {
                defaultValue = " ";
            }

            if (type.toUpperCase().contains("VARCHAR")) {
                defaultValue = " '" + defaultValue + "' ";
            }

            String update = "UPDATE %s SET %s = %s WHERE %s IS NULL;";
            update = String.format(update, tableDBName, name, defaultValue, name);
            sb.append(update);
            sb.append("\n");

            String sql = "ALTER TABLE %s MODIFY %s DEFAULT %s NOT NULL;";
            if (!"Y".equals(must)) {
                sql = "ALTER TABLE %s MODIFY %s DEFAULT %s;";
            }


            sql = String.format(sql, tableDBName, name, defaultValue);
            sb.append(sql);
            sb.append("\n");
        }
        return sb.toString();
    }


    /**
     * 通用TTTTTT
     * 获取Connection(oracle)
     *
     * @return
     */
    public static Connection getConn(DBConnModel dbConnModel) {
        try {
            //URL指向要访问的数据库名mydata
            String url = dbConnModel.getDBUrl();
            //驱动程序名
            String driver = dbConnModel.getDBDriver();
            //MySQL配置时的用户名
            String user = dbConnModel.getDBUser();
            //MySQL配置时的密码
            String password = dbConnModel.getDBPassword();

            //加载驱动程序
            Class.forName(driver);
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            System.out.println("连接出错！！！");
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 通用TTTTTT
     * 获取Connection(oracle)
     *
     * @return
     */
    public static Connection getConn() {
        try {
            //URL指向要访问的数据库名mydata
            String url = "jdbc:oracle:thin:@//192.168.1.107:1521/orcl";
            //驱动程序名
            String driver = "oracle.jdbc.driver.OracleDriver";
            //MySQL配置时的用户名
            String user = "dlsb";
            //MySQL配置时的密码
            String password = "dlsb";

            //加载驱动程序
            Class.forName(driver);
            return DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            System.out.println("连接出错！！！");
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 通用TTTTTT
     * 获取Resultset
     *
     * @param con
     * @param sql
     * @return
     * @throws Exception
     */
    public static ResultSet getResultSet(Connection con, String sql) throws Exception {

        if (!con.isClosed()) {
            System.out.println("Succeeded connecting to the Database!");
        }
        //2.创建statement类对象，用来执行SQL语句！！
        Statement statement = con.createStatement();
        //要执行的SQL语句

        //3.ResultSet类，用来存放获取的结果集！！
        ResultSet rs = statement.executeQuery(sql);
        return rs;
    }

    /**
     * 通用TTTTTT
     * 关闭数据库
     *
     * @param con
     * @param rs
     * @throws Exception
     */
    public static void closeDB(Connection con, ResultSet rs) throws Exception {
        if (rs != null) {
            rs.close();
        }
        if (con != null) {
            con.close();
        }
    }


    /**
     * 专用zzzzzzzzzzzzzz
     * 查询某表的数据
     *
     * @param con
     * @param tableName
     * @return
     * @throws Exception
     */
    public static ResultSet getOralceTableColumnInfo(Connection con, String tableName) throws Exception {

        if (!con.isClosed()) {
//            System.out.println("Succeeded connecting to the Database!");
        }
        //2.创建statement类对象，用来执行SQL语句！！
        Statement statement = con.createStatement();
        //要执行的SQL语句
        String sql = "SELECT\n" +
                "\tT .column_name AS name,\n" +
                "\t--列名\n" +
                "\tT .column_type AS type,\n" +
                "\t---字段类型\n" +
                "\tT .data_length AS length,\n" +
                "\t--字段长度\n" +
                "\tT .data_scale AS scale,\n" +
                "\t--字段精度\n" +
                "\tT .column_comment AS commentate,\n" +
                "\t--字段注释\n" +
                "\n" +
                "b.constraint_type AS is_pk,\n" +
                " --是否主键\n" +
                "DECODE (\n" +
                "\tT .nullable,\n" +
                "\t'N',\n" +
                "\tT .nullable,\n" +
                "\tNULL\n" +
                ") is_d_b_must   --是否为空\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tUB.tablespace_name AS database_name,\n" +
                "\t\t\tUTC.table_name AS table_name,\n" +
                "\t\t\tUTC.column_name AS column_name,\n" +
                "\t\t\tNVL(utc.data_precision,UTC.data_length) as data_length,\n" +
                "\t\t\tUTC.data_type AS column_type,\n" +
                "\t\t\tutc.data_scale AS data_scale,\n" +
                "\t\t\tucc.comments AS column_comment,\n" +
                "\t\t\tutc.column_id,\n" +
                "\t\t\tutc.nullable\n" +
                "\t\tFROM\n" +
                "\t\t\tuser_tables ub\n" +
                "\t\tLEFT JOIN user_tab_columns utc ON ub.table_name = UTC.table_name\n" +
                "\t\tLEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name\n" +
                "\t\tAND utc.table_name = ucc.table_name\n" +
                "\t) T\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tUCC.table_name AS table_name,\n" +
                "\t\tucc.column_name AS column_name,\n" +
                "\t\twm_concat (UC.constraint_type) AS constraint_type\n" +
                "\tFROM\n" +
                "\t\tuser_cons_columns ucc\n" +
                "\tLEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name\n" +
                "\tGROUP BY\n" +
                "\t\tUCC.table_name,\n" +
                "\t\tucc.column_name\n" +
                ") b ON T .table_name = b.TABLE_NAME\n" +
                "AND T .column_name = b.column_name\n" +

                "where T.table_name='" + tableName + "' \n" +
                "order by T.column_id";
//        System.out.println(sql);
        //3.ResultSet类，用来存放获取的结果集！！
        ResultSet rs = statement.executeQuery(sql);
        return rs;
    }

    /**
     * 获取表中字段信息
     *
     * @param con 连接
     * @param sql 要执行的sql
     * @return
     * @throws Exception
     */
    public static ResultSet executeQuery(Connection con, String sql) throws Exception {
        //2.创建statement类对象，用来执行SQL语句！！
        Statement statement = con.createStatement();
        //3.ResultSet类，用来存放获取的结果集！！
        ResultSet rs = statement.executeQuery(sql);
        return rs;
    }

    /**
     * 所有的表
     *
     * @return
     */
    public static String getIndSql() {
        return getIndSql("");
    }

    /**
     * @param tableName 表名，若为空，则为所有
     * @return
     */
    public static String getIndSql(String tableName) {
        //要执行的SQL语句
        String sql = "select S.TABLE_NAME AS tableName, S.INDEX_NAME AS indexName, \n" +
                "S.COLUMN_NAME AS cloumnName, S.COLUMN_POSITION AS columnPosition, \n" +
                "S.DESCEND AS descend \n" +
                "from user_ind_columns S \n";
        if (!DevComUtil.isEmpty(tableName)) {
            sql = sql + "WHERE s.table_name = '" + tableName + "'\n";
        }
        sql = sql + "ORDER BY S.TABLE_NAME, S.INDEX_NAME ,S.COLUMN_POSITION ";
        return sql;
    }

    /**
     * 所有的表
     *
     * @return
     */
    public static String getFieldSql() {
        return getFieldSql("");
    }


    /**
     * @param tableName 表名，若为空，则为所有
     * @return
     */
    public static String getFieldSql(String tableName) {
        //要执行的SQL语句
        String sql = "SELECT\n" +
                "T.table_name, --表名\n" +
                "T.comments as table_comments, --表名注释\n" +
                "\tT .column_name AS name,--列名\n" +
                "\tT .column_comment AS commentate,--字段注释\n" +
                "\tT .data_length AS length,--字段长度\n" +
                "\tT .data_scale AS scale,--字段精度\n" +
                "\tT .column_type AS type,---字段类型\n" +
                "DECODE (T .nullable,'N',T .nullable,NULL) is_d_b_must,--是否为空\n" +
                "b.constraint_type AS is_pk,--是否主键\n" +
                "\tT .data_defautl AS defaultValue--默认值\n" +
                "FROM\n" +
                "(\n" +
                "SELECT\n" +
                "UB.tablespace_name AS database_name,\n" +
                "UTC.table_name AS table_name,\n" +
                "UTC.column_name AS column_name,\n" +
                "NVL(utc.data_precision,UTC.data_length) AS data_length,\n" +
                "UTC.data_type AS column_type,\n" +
                "utc.data_scale AS data_scale,\n" +
                "ucc.comments AS column_comment,\n" +
                "utc.column_id,\n" +
                "utc.nullable,\n" +
                "utc.DATA_DEFAULT AS data_defautl,\n" +
                "co.comments\n" +
                "FROM\n" +
                "user_tables ub\n" +
                "LEFT JOIN user_tab_columns utc ON ub.table_name = UTC.table_name\n" +
                "LEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name AND utc.table_name = ucc.table_name \n" +
                "LEFT JOIN user_tab_comments co ON co.table_name = ub.table_name\n" +
                ") T\n" +
                "LEFT JOIN (\n" +
                "SELECT\n" +
                "UCC.table_name AS table_name,\n" +
                "ucc.column_name AS column_name,\n" +
                "wm_concat (UC.constraint_type) AS constraint_type\n" +
                "FROM\n" +
                "user_cons_columns ucc\n" +
                "LEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name\n" +
                "GROUP BY\n" +
                "UCC.table_name,\n" +
                "ucc.column_name\n" +
                ") b ON T .table_name = b.TABLE_NAME\n" +
                "\n" +
                "AND T .column_name = b.column_name\n";
        if (!DevComUtil.isEmpty(tableName)) {
            sql = sql + "WHERE t.table_name = '" + tableName + "'\n";
        }
        sql = sql + "order by T.table_name, T.column_id";
        return sql;
    }

}
